﻿using LFramework.Data;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace DataServices
{
    /*
     * 陈伟
     * 20140831
     * 顾问操作类
     */
    public class DaoAdviser
    {
        IDBHelper dbhelper = DBFactory.CreateFromConfig("conn");

        /// <summary>
        /// 根据顾问id显示顾问明细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AdviserInfo LoadAdviserByID(string id)
        {
            string sql = "select * from tbAdviser where AdviserID = '" + id + "'";

            DataRow dr = dbhelper.QueryForDataTable(sql).Rows[0];

            AdviserInfo adviser = new AdviserInfo()
            {
                AdviserID = dr["AdviserID"].ToString(),
                AdviserName = dr["AdviserName"].ToString(),
                Abstract = dr["Abstract"].ToString()
            };


            DataTable dtAtta = dbhelper.QueryForDataTable("select * from tbNewsAttachment where NewsId = '" + id + "'");

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                adviser.AttachmentList.Add(attachment);
            } 

            return adviser;
        }

        /// <summary>
        /// 查询所有顾问
        /// </summary>
        /// <returns></returns>
        public IList<AdviserInfo> LoadAllAdvisers()
        {
            IList<AdviserInfo> list = new List<AdviserInfo>();
            string sql = "select * from tbAdviser";
            DataTable dt = dbhelper.QueryForDataTable(sql);
            foreach (DataRow dr in dt.Rows)
            {
                AdviserInfo adviser = new AdviserInfo()
                {
                    AdviserID = dr["AdviserID"].ToString(),
                    AdviserName = dr["AdviserName"].ToString(),
                    Abstract = dr["Abstract"].ToString()
                };

                //查询顾问对应的图片信息
                DataTable dtAtta = dbhelper.QueryForDataTable("select * from tbNewsAttachment where NewsId = '" + adviser.AdviserID + "'");
                if (dtAtta.Rows.Count > 0)
                {
                    foreach (DataRow drAtta in dtAtta.Rows)
                    {
                        AttachmentInfo attachment = new AttachmentInfo();
                        attachment.FileName = drAtta["FileName"].ToString();
                        attachment.SaveName = drAtta["SaveName"].ToString();
                        adviser.AttachmentList.Add(attachment);
                    }
                }

                list.Add(adviser);
            }
            return list;
        }

        public void AddAdviser(AdviserInfo adviser)
        {
            string sql = @"insert into tbAdviser(
                            [AdviserID]
                           ,[AdviserName]
                           ,[Abstract]) 
                           values (
                            @AdviserID,
                            @AdviserName,
                            @Abstract
                            )  
                           ";

            OleDbParameter[] paras = { new OleDbParameter("AdviserID",adviser.AdviserID),
                                     new OleDbParameter("AdviserName", adviser.AdviserName) ,
                                     new OleDbParameter("Abstract", adviser.Abstract)
                                   };

            dbhelper.ExecuteSQL_InTrans(sql, paras);

            foreach (var item in adviser.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + adviser.AdviserID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }

        public void UpdateAdviser(AdviserInfo adviser)
        {
            string sql = @"
              update tbAdviser 
             set AdviserName = @AdviserName , Abstract = @Abstract
             where AdviserID = '" + adviser.AdviserID + "'";

            OleDbParameter[] paras = { new OleDbParameter("AdviserName", adviser.AdviserName) , 
                                       new OleDbParameter("Abstract",adviser.Abstract),
                                   };

            dbhelper.ExecuteSQL(sql, paras);

            //更新附件列表
            sql = "delete from tbNewsAttachment where NewsId = '" + adviser.AdviserID + "'";
            dbhelper.ExecuteSQL(sql);

            foreach (var item in adviser.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + adviser.AdviserID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }

        public void DeleteAdviser(string id)
        {
            string sql = "delete from tbNewsAttachment where NewsId = '" + id + "'";
            dbhelper.ExecuteSQL(sql);
            sql = "delete from tbAdviser where AdviserID = '" + id + "'";
            dbhelper.ExecuteSQL(sql);
        }
    }
}
